<?php
session_start();
require_once "include/database.php";
$db = db_connect();
require_once "include/auth.php";
$current_user = current_user();
require_login();
require_user(1);

$title = "CS 4400 Project | Faculty Report";

include "_header.php";

// Get the course that the professor is teaching.
$crnquery = $db->prepare("SELECT CRN, sec.Instructor_ID FROM section AS sec
                          JOIN faculty AS fac ON sec.Instructor_ID = fac.Instructor_ID 
                          WHERE fac.Instructor_ID = :facultyid");
$crnquery->execute(array("facultyid" => $current_user['facultyid']));

// Students in each section the professor is teaching.
$studentsinsection = $db->prepare("SELECT  SC.CRN, SC.Code AS CourseCode, SC.Title as Title, 
                                  AVG(CASE WHEN (-1*(ASCII(reg.Grade_Received) - 69) < 0) THEN 0 ELSE 
                                  ABS(ASCII(reg.Grade_Received) - 69) END) AS Grade, CASE WHEN 
                                  ISNULL(LV.Student_ID) THEN 0 WHEN SC.CRN = LV.CRN THEN 
                                  COUNT(reg.Student_ID) ELSE 0 END AS NoMeetings
                                  FROM registers AS reg
                                  LEFT JOIN SectionComplete AS SC ON reg.CRN = SC.CRN
                                  LEFT JOIN logs_visit AS LV ON reg.Student_ID = LV.Student_ID WHERE SC.CRN = :crn GROUP BY reg.CRN,reg.Student_ID");

$coursecode = "";
$classtitle = "";
$grades = array(0, 0, 0);
$counter = array(0, 0, 0);
while ($crnresult = $crnquery->fetch()) {
    $crn = $crnresult['CRN'];
    $studentsinsection->execute(array("crn" => (int)$crn));
    while ($student = $studentsinsection->fetch()) {
        $coursecode = $student['CourseCode'];
        $classtitle = $student['Title'];
        if ($student['NoMeetings'] > 3) {
            $grades[0] += $student['Grade'];
            $counter[0]++;
        }
        else if ($student['NoMeetings'] <= 3 && $student['NoMeetings'] >= 1) {
            $grades[1] += $student['Grade'];
            $counter[1]++;
        }
        else {
            $grades[2] += $student['Grade'];
            $counter[2]++;
        }
    }
}

if ($counter[0] === 0) { $counter[0] = 1;}
if ($counter[1] === 0) { $counter[1] = 1;}
if ($counter[2] === 0) { $counter[2] = 1;}
echo "<div class='container'>";
echo "<h2 style='text-align:center;'> Faculty Report </h2>";
echo "<table class='table table-bordered table-hover'>";
echo "<thead style='background-color:darkgrey'><tr>" .
    "<td>Course Code</td>" . 
    "<td>Course Name</td>" .
    "<td>No. of Meetings with Tutor</td>" .
    "<td>Average Grade</td>" .
    "</tr></thead>";
echo "<tr>";
echo "<td>" . $coursecode . "</td>";
echo "<td>" . $classtitle . "</td>";
echo "<td><table class='table table-bordered'><tr><td>More than 3</td></tr><tr><td>1-3</td></tr><tr><td>None</td></tr></td></table>";
echo "<td><table class='table table-bordered'><tr><td>" . $grades[0]/$counter[0] . "</td></tr><tr><td>" 
                    . $grades[1]/$counter[1] . "</td></tr><tr><td>"
                    . $grades[2]/$counter[2] . "</td></tr></table></td>";
echo "</tr>";
echo "</table></div>";

//

?>